<?php

class Crud extends ArrayObject {
	public $table;
	public $alias;
	public $pk;
	public $fk;
	public $childObj;
	public $ORM; //orm relationship
	public $fields = array();
	public $exclude = array();
	
	public function __construct($table, $alias = NULL, $obj = NULL, $config = NULL, $exclude = NULL) {
		$this->table = $table;
		if(isset($alias) && !empty($alias)) $this->alias = $alias;
		if(is_object($obj)) $this->join($obj,$config);
		if($pk = DB::$db->getPrimaryKey($table)) {
			$this->pk = $pk;
			if($fk = DB::$db->getForeignKey($table,$pk)) $this->fk = $fk;
		}
		if(isset($exclude) && is_array($exclude) && count($exclude) > 0) $this->exclude = $exclude;
		return TRUE;
	}
		
	/* find(mixed $data)  - makes record matches
	 *
	 * @access public
	 * @param  array - criteria to make a match
	 * @return mixed uniqueId or array of records/false
	 */
	public function find(array $data) {
		//default locar vars
		$select = '*';
		$order = NULL;
		$group = NULL;
		$having = NULL;
		$limit = NULL;
		$criteria = NULL;
		$join = NULL;
		
		$keys = array_keys($data);
		if(in_array('select',$keys) || in_array('SELECT',$keys)) {
			//triggered complex query.
			if(isset($data['SELECT'])) {
				$data['select'] = $data['SELECT'];
				unset($data['SELECT']);
			}
			if(is_array($data['select'])) {
				$select = implode(',',$data['select']);
			} else {
				$select = $data['select'];
			}
			unset($data['select']);
		}
		if(in_array('order',$keys) || in_array('order by',$keys) || in_array('ORDER',$keys) || in_array('ORDER BY',$keys)) {
			if(isset($data['order'])) {
				$order = $data['order'];
				unset($data['order']);
			} elseif(isset($data['order by'])) {
				$order = $data['order by'];
				unset($data['order by']);
			} elseif(isset($data['ORDER'])) {
				$order = $data['ORDER'];
				unset($data['ORDER']);
			} elseif(isset($data['ORDER BY'])) {
				$order = $data['ORDER BY'];
				unset($data['ORDER BY']);
			}
			if(is_array($order) && !empty($order)) {
				$oSize = count($order);
				foreach($order as $okey => $field) {
					if(is_array($field)) {
						if(isset($field['field'])) $tmp .= $field['field'];
						if(isset($field['direction'])) $tmp .= ' '. $field['direction'];
					} else {
						$tmp .= $field;
					}
					if(($okey+1) < $oSize) $tmp .= ',';					
				}
				$order = $tmp;
				unset($tmp,$field,$oSize,$okey);
			}
		}
		if(in_array('group',$keys) || in_array('group by', $keys) || in_array('GROUP',$keys) || in_array('GROUP BY', $keys)) {
			if(isset($data['group'])) {
				$group = $data['group'];
				unset($data['group']);
			} elseif(isset($data['group by'])) {
				$group = $data['group by'];
				unset($data['group by']);
			} elseif(isset($data['GROUP'])) {
				$group = $data['GROUP'];
				unset($data['GROUP']);
			} elseif(isset($data['GROUP BY'])) {
				$group = $data['GROUP BY'];
				unset($data['GROUP BY']);
			}
			if(is_array($group) && !empty($group)) {
				$group = implode(',',$group);
			}
		}
		if(in_array('having',$keys) || in_array('HAVING',$keys)) {
			if(isset($data['HAVING'])) {
				$data['having'] = $data['HAVING'];
				unset($data['HAVING']);
			}
			if(is_array($data['having'])) {
				$data['having'] = DB::$db->clean($data['having']);
				$hCount = count($data['having']);
				foreach($data['having'] as $key => $val) {
					$having .= $key . " = '$val'";
					if(($key+1) < $hCount) $having .= 'AND ';
				}
				unset($data['having'],$hCount);				
			} elseif(is_string($data['having'])) {
				$having = $data['having'];			
			}
			unset($data['having']);
		}
		if(in_array('limit',$keys) || in_array('LIMIT',$keys)) {
			if(isset($data['LIMIT'])) {
				$data['limit'] = $data['LIMIT'];
				unset($data['LIMIT']);
			}
			if(is_array($data['limit'])) {
				switch(count($data['limit'])) {
					case '1':
						$limit = $data['limit'][0];
					break;
					case '2':
						$limit = implode(',',$data['limit']);
					break;
				}
			} elseif(is_numeric($data['limit'])) {
				$limit = $data['limit'];
			}
			unset($data['limit']);
		}
		if(in_array('where',$keys)) {
			$data = $data['where'];
		} elseif(in_array('WHERE',$keys)) {
			$data = $data['WHERE'];
		}
		if(is_array($data) && !empty($data)) {
			$data = DB::$db->clean($data);
			$fields = $this->getFieldsComplex();
			foreach($data as $key => $val) {
				$arr = explode('.',$key);
				if(count($arr) < 2) {
					unset($data[$key]);
					$key = $this->table.'.'.$key;
					$data[$key] = $val;
				}
				if(!in_array($key,$fields) || in_array($key,$this->exclude)) {
					unset($data[$key]);
				}
			}

			$wCount = count($data);
			foreach($data as $key => $val) {
				$criteria .= $key.' = '.$val;
				if((@$counter + 1) < $wCount) $criteria .= ' AND ';
				@$counter++;
			}
		} elseif(is_string($data)) {
			$criteria = $data;
		}
		try {
			if(isset($this->ORM) && !empty($this->ORM)) $join = $this->joinToSql();
			$sql = 'SELECT '.$select.' FROM '.$this->table;
			if(isset($this->alias) && !empty($this->alias)) $sql .= ' '. $this->alias;
			if(isset($join) && !empty($join)) $sql .=  ' '.$join;
			if(isset($criteria) && !empty($criteria)) $sql .= ' WHERE '.$criteria;
			if(isset($group) && !empty($group)) $sql .= ' GROUP BY '.$group;
			if(isset($having) && !empty($having)) $sql .= ' HAVING '.$having;
			if(isset($order) && !empty($order)) $sql .= ' ORDER BY '.$order;
			if(isset($limit) && !empty($limit)) $sql .= ' LIMIT '.$limit;
			
			//echo $sql;
			$res = DB::$db->query($sql);
			return $res;
		} catch (Exception $e) {
			echo 'Problem querying [sql: '.$sql.' '.$e->getMessage().']';
		}
		return FALSE;
	}
	
	
	/* getFields()  - gets the fields for the table
	 *
	 * @access public
	 * @param  nill
	 * @return mixed array, false
	 */
	 public function getFields() {
		if(is_array($this->fields) && count($this->fields) > 0) return $this->fields; //lazy load
		if($resFields = DB::$db->fetchFields($this->table)) {
			foreach($resFields as $field) {
				$this->fields[] = $this->table.'.'.$field;
			}
			return $this->fields;
		}
		return FALSE;
	 }
	 
	 
	/* getFieldsComplex()  - gets the fields for the table, replaces with alias if it exists
	 *
	 * @access public
	 * @param  nill
	 * @return mixed array, false
	 */
	 public function getFieldsComplex() {
		if($resFields = DB::$db->fetchFields($this->table)) {
			if(isset($this->alias) && !empty($this->alias)) {
				foreach($resFields as $field) {
					$fields[] = $this->alias.'.'.$field;
				}
			} else {
				foreach($resFields as $field) {
					$fields[] = $this->table.'.'.$field;
				}
			}
			return $fields;
		}
		return FALSE;
	 }
	 
	 
	/* getSimpleFields()  - gets the fields for the table
	 *
	 * @access public
	 * @param  nill
	 * @return mixed array, false
	 */
	 public function getShortFields() {
		if($resFields = DB::$db->fetchFields($this->table)) {
			foreach($resFields as $field) $fields[] = $field;
			return $fields;
		}
		return FALSE;
	 }


	/* add(array $data)  - adds a record
	 *
	 * @access public
	 * @param  array - array of required firm data to suffice a complete entry
	 * @return bool
	 */
	public function add(array $data) {
		//if oneToMany relationships exist, attempt to add data to child tables first, and associate the result PK to current data
		if(is_array($this->childObj)) {
			foreach($this->childObj as $ckey => $child) {
				if(isset($this->ORM[$ckey]->relationship)) {
					switch($this->ORM[$ckey]->relationship) {
						case 'oneToMany':
							if($this->childObj[$ckey]->pk) {
								if($aRes = $this->childObj[$ckey]->find($data)) {
									$data[$this->childObj[$ckey]->pk] = $aRes[$this->childObj[$ckey]->pk];
								} elseif($pRes = $this->childObj[$ckey]->add($data)) {
									$data[$this->childObj[$ckey]->pk] = $pRes;
								} else {
									return FALSE;
								}
							}
						break;
					}
				}
			}
		}
		$fields = $this->getFields();
		$origData = $data;
		foreach($data as $key => $val) {
			$arr = explode('.',$key);
			if(count($arr) < 2) {
				unset($data[$key]);
				$key = $this->table.'.'.$key;
				$data[$key] = $val;
			}
			if(!in_array($key,$fields) || in_array($key,$this->exclude)) {
				unset($data[$key]);
			}
		}
		if(count($data) > 0) {
			try {
				DB::$db->insert($this->table, $data);
				$id = $this->lastInsertedId();
				//if manyToOne relationships exist, add current table's PK to data then attempt to add on child tables
				if(is_array($this->childObj)) {
					foreach($this->childObj as $ckey => $child) {
						if(isset($this->ORM[$ckey]->relationship)) {
							switch($this->ORM[$ckey]->relationship) {
								case 'manyToMany':
								case 'manyToOne':
								case 'oneToOne':
									if($this->pk) {
										$origData[$this->pk] = $id;
										$this->childObj[$ckey]->add($origData);
									}
								break;
							}
						}
					}
				}
				return $id;
			} catch(Exception $e) {
				echo 'Problem adding ['.$e->getMessage().']';
			}
		}
		return FALSE;
	}
	
	/* lastInsertedId()  - gets last inserted uniqueID from table
	 *
	 * @access public
	 * @return id, false
	 */
	public function lastInsertedId() {
		if($res = DB::$db->lastInsertedID()) return $res;
		return FALSE;
	}


	/* update(array $data, array $matches)  - updated record
	 *
	 * @access public
	 * @params int, array - array of data to update uniqueId
	 * @return bool
	 */
	public function update($data, array $matches) {
		if(isset($this->ORM) && !empty($this->ORM)) {
			$join = $this->joinToSql();
			$sql = 'UPDATE '.$this->table;
			if(isset($this->alias) && !empty($this->alias)) $sql .= ' '.$this->alias;
			if(isset($join) && !empty($join)) $sql .=  ' '.$join;
			
			//set
			if(count($data) > 0) {
				$data = DB::$db->clean($data);
				$set = ' SET';
				$rc = count($data);
		        $j = 0;
		        foreach ($data as $field => $val) {
		            $set .= ' '.$field.' = '.$val.' ';
		            if(($j+1) < $rc) $set .= ',';
		            $j++;
		        }
		        $sql .= $set;
		        
		        //matches -> criteria
		        $rc = count($matches);
		        $j = 0;
		        foreach ($matches as $field => $val) {
		            $criteria .= " ".$field." = $val ";
		            if(($j+1) < $rc) $criteria .= 'AND';
		            $j++;
		        }
				if(isset($criteria) && !empty($criteria)) $sql .= " WHERE".$criteria;
				
				//debug
				//echo $sql;
				try {
					DB::$db->query($sql);
					return TRUE;
				} catch (Exception $e) {
					echo 'Problem updating ['.$e->getMessage().']';
				}
			}
		} else {
			$fields = $this->getShortFields();
			foreach($data as $key => $val) {
				if(!in_array($key,$fields) || empty($val) || in_array($key,$this->exclude)) {
					unset($data[$key]);
				}
			}
			if(count($data) > 0) {
				try {
					DB::$db->update($this->table, $data, $matches);
					return TRUE;
				} catch (Exception $e) {
					echo 'Problem updating ['.$e->getMessage().']';
				}
			}
		}
		return FALSE;
	}


	/* delete(array $matches)  - 
	 *
	 * @access public
	 * @param array - key val pair of matching criteria to perform the action
	 * @return bool
	 */
	public function delete(array $matches) {
		if(isset($this->ORM) && !empty($this->ORM)) {
			$join = $this->joinToSql();
			$sql = 'DELETE '.$this->table.'.* FROM '.$this->table;
			if(isset($this->alias) && !empty($this->alias)) $sql .= ' '.$this->alias;
			if(isset($join) && !empty($join)) $sql .=  ' '.$join;
			
		        
	        //matches -> criteria
	        $rc = count($matches);
	        $j = 0;
	        foreach ($matches as $field => $val) {
	            $criteria .= " ".$field." = $val ";
	            if(($j+1) < $rc) $criteria .= 'AND';
	            $j++;
	        }
			if(isset($criteria) && !empty($criteria)) $sql .= " WHERE".$criteria;
			
			//debug
			//echo $sql;
			try {
				DB::$db->query($sql);
				return TRUE;
			} catch (Exception $e) {
				echo 'Problem deleting ['.$e->getMessage().']';
			}
		} else {
			try {
				DB::$db->delete($this->table, $matches);
				return TRUE;
			} catch (Exception $e) {
				echo 'Problem deleting ['.$e->getMessage().']';
			}
		}
		return FALSE;
	}
	
	/* join(Crud $obj)	- sets the ORM obj as a conf
	 * 
	 * @access public
	 * @param  object
	 * @return void
	 */
	public function join(Crud $obj,$config = '') {
		$this->childObj[] = $obj;
		$this->ORM[] = new ORM($obj->table,$this->table,$config);
	}
	
	/* unjoin(Crud $obj) 	- unsets the joined ORM obj
	 * 
	 * @access public
	 * @param  object
	 * @return bool
	 */
	public function unjoin(Crud $obj) {
		if(isset($this->childObj) && $this->childObj == $obj) {
			unset($this->childObj);
			return TRUE;
		}
		return FALSE;
	}
	
	/* joinToSql - recusive magic that traverses ORM to create a valid join string for queries
	 *
	 * @access private
	 * @param  object
	 * @return string
	 */
	private function joinToSql($childObj = '', $ORM = '') {
		if(isset($childObj) && !empty($childObj) && isset($ORM) && !empty($ORM)) { //recursive
			if(is_array($childObj) && count($childObj) > 1) { //many relationships
				foreach($childObj as $ckey => $child) {
					foreach($ORM[$ckey]->joinCriteria as $key => $val) {
						$join .= 'LEFT JOIN `'.$ORM[$ckey]->parentTable.'`';
						if(isset($child->childObj->alias) && !empty($child->childObj->alias)) {
							$val = $child->childObj->alias .'.'.$val;
						}
						if(isset($childObj[0]->alias) && !empty($childObj[0]->alias)) {
							$join .= ' '.$childObj[0]->alias;
						}
						$join .= ' ON '.$key.' = '.$val.' ';
						if(isset($child->ORM) && !empty($child->ORM)) $join .= $this->joinToSql($child->childObj,$child->ORM);
					}
				}
			} elseif(is_array($childObj)) {
				foreach($ORM[0]->joinCriteria as $key => $val) {
					$join .= 'LEFT JOIN `'.$ORM[0]->parentTable.'`';
					if(isset($childObj[0]->childObj->alias) && !empty($childObj[0]->childObj->alias)) {
						$val = $childObj[0]->childObj->alias .'.'.$val;
					}
					if(isset($childObj[0]->alias) && !empty($childObj[0]->alias)) {
						$join .= ' '.$childObj[0]->alias;
					}
					$join .= ' ON '.$key.' = '.$val.' ';
					if(isset($childObj[0]->ORM) && !empty($childObj[0]->ORM)) $join .= $this->joinToSql($childObj[0]->childObj,$childObj[0]->ORM);
				}
			} 
		} else { //first run
			if(is_array($this->childObj) && count($this->childObj) > 1) {
				foreach($this->childObj as $ckey => $child) {
					foreach($this->ORM[$ckey]->joinCriteria as $key => $val) {
						$join .= 'LEFT JOIN `'.$this->ORM[$ckey]->parentTable.'`';
						if(isset($child->alias) && !empty($child->alias)) {
							$val = $child->alias .'.'.$val;
						}
						if(isset($this->alias) && !empty($this->alias)) {
							$join .= ' '.$this->alias;
						}
						$join .= ' ON '.$key.' = '.$val.' ';
						if(isset($child->childObj[0]->ORM) && !empty($child->childObj[0]->ORM)) $join .= $this->joinToSql($child->childObj, $child->ORM);
					}
				}
			} elseif(is_array($this->childObj)) { //only 1 relationship
				foreach($this->ORM[0]->joinCriteria as $key => $val) {
					$join .= 'LEFT JOIN `'.$this->ORM[0]->parentTable.'`';
					if(isset($this->childObj[0]->alias) && !empty($this->childObj[0]->alias)) {
						$val = $this->childObj[0]->alias .'.'.$val;
					}
					if(isset($this->alias) && !empty($this->alias)) {
						$join .= ' '.$this->alias;
					}
					$join .= ' ON '.$key.' = '.$val.' ';
					if(isset($this->childObj[0]->ORM) && !empty($this->childObj[0]->ORM)) $join .= $this->joinToSql($this->childObj, $this->ORM);
				}
			}
		}
		return $join;
	}
	
	/*Transaction methods*/
	public function begin() {
		DB::$db->transaction('begin');
	}

	public function commit() {
		DB::$db->transaction('commit');
	}
	
	public function rollback() {
		DB::$db->transaction('rollback');
	}
	
	
}

?>